/****** Object:  Stored Procedure dbo.asi_ResultPage    Script Date: 7/3/2003 4:58:05 PM ******/



CREATE    proc [dbo].[asi_ResultPage]
            @select nvarchar(4000),
            @offset int = 0,
            @pageSize int = 10,
            @returnRowCount bit = 0 As
begin
-- ***************************************************************************************
-- Declare variables
-- ***************************************************************************************
declare @countSelect nvarchar(4000)
declare @limiter int
declare @posFrom int
declare @posOrderBy int
declare @posSelectList int
declare @tmp nvarchar(4000)
declare @tmpTable char(38)
-- <SQL_7>
declare @tmpTable2 char(38)
-- </SQL_7>
declare @tmpTable3 char(38)
declare @upperSelect nvarchar(4000)
-- ***************************************************************************************
-- Calculate Limiter Value
-- ***************************************************************************************
set @limiter = 500
if (@offset) >= (@limiter - @pageSize)
begin
set @limiter = @offset + (@pageSize * 2)
end
-- ***************************************************************************************
-- Name Temp Tables
-- ***************************************************************************************
set @tmpTable = N'#z' + replace(cast(newid() As nchar(36)),N'-',N'')
-- <SQL_7>
set @tmpTable2 = N'#z' + replace(cast(newid() As nchar(36)),N'-',N'')
-- </SQL_7>
set @tmpTable3 = N'#z' + replace(cast(newid() as nchar(36)),N'-',N'')
-- ***************************************************************************************
-- Clean up select statement
-- ***************************************************************************************
set @select = ltrim(@select)
set @select = replace(@select,nchar(9),N' ')
set @select = replace(@select,nchar(10),N' ')
set @select = replace(@select,nchar(13),N' ')
-- ***************************************************************************************
-- Convert to uppercase for pattern searching
-- ***************************************************************************************
set @upperSelect = upper(@select)
-- ***************************************************************************************
-- Find position of first item to be displayed in resultset
-- ***************************************************************************************
if charindex(' DISTINCT ', @upperSelect) = 0
begin
    set @posSelectList = charindex(N' ', @upperSelect) + 1
end
else
begin
    set @posSelectList = charindex(N' DISTINCT ', @upperSelect) + 10
end
-- ***************************************************************************************
-- Find position of first object in from clause
-- ***************************************************************************************
set @posFrom = charindex(N' FROM ', @upperSelect)
-- ***************************************************************************************
-- Find position order by clause
-- ***************************************************************************************
set @posOrderBy = charindex(N' ORDER BY ', @upperSelect)
-- ***************************************************************************************
-- Copy select statment without the order by clause to be used for returning a count
-- ***************************************************************************************
set @countSelect = @select
if  @posOrderBy > 0
begin
    set @countSelect = left(@countSelect, @posOrderBy)
end
set @countSelect = stuff(@countSelect, @posSelectList, @posFrom - @posSelectList, N' count(*) AS Row_Count ')
-- ***************************************************************************************
-- If @pageSize = 0 or @offset = 0
-- ***************************************************************************************
if @offset = 0 or @pageSize = 0
begin
    set @tmp = @select
    set @tmp = stuff(@tmp,@posSelectList,0,N'cast(0 as int) As ResultRow,')
    if @pageSize > 0
    begin
        set @tmp = stuff(@tmp,@posSelectList,0,N'TOP ' + cast(@pageSize As varchar(10)) + N' ')
    end
    set @tmp = N'set nocount on'+nchar(13)+nchar(10)+@tmp
    if  @returnRowCount <> 0 and @pageSize = 0
    begin
        set @tmp = @tmp+nchar(13)+nchar(10)+N'select @@ROWCOUNT AS Row_Count'
    end
    if  @returnRowCount <> 0 and @pageSize <> 0
    begin
        set @countSelect = @select
        if  @posOrderBy > 0
        begin
            set @countSelect = left(@countSelect,@posOrderBy)
        end
        set @countSelect = stuff(@countSelect,@posSelectList,@posFrom - @posSelectList,N' TOP '+cast(@limiter As varchar(10))+N' 0 AS rc INTO '+@tmpTable3)
        set @countSelect = @countSelect+nchar(13)+nchar(10)+N'select @@ROWCOUNT AS Row_Count'
        set @countSelect = @countSelect+nchar(13)+nchar(10)+N'drop table '+@tmpTable3
        --set @countSelect = stuff(@countSelect,@posSelectList,@posFrom - @posSelectList,N' count(*) AS Row_Count ')
        set @tmp = @tmp+nchar(13)+nchar(10)+@countSelect
    end
    set @tmp = @tmp+nchar(13)+nchar(10)+N'set nocount off'
end
else
begin --@offset <> 0 and @pageSize <> 0
-- ***************************************************************************************
-- Copy select statement for inserting into the temp table
-- ***************************************************************************************
set @tmp = @select
set @tmp = stuff(@tmp,@posFrom,0,N' into '+@tmpTable)
-- <SQL_7>
set @tmp = stuff(@tmp,@posSelectList,0,N'top '+convert(nvarchar(10),(@offset+@pageSize))+N' ')
-- </SQL_7>
-- <SQL_2000>
-- set @tmp = stuff(@tmp,@posSelectList,0,N'top '+convert(nvarchar(10),(@offset+@pageSize))+N' identity(int,1,1) As ResultRow,')
-- </SQL_2000>
-- ***************************************************************************************
-- Create the statement that will do the work
-- ***************************************************************************************
set @tmp = N'set nocount on'+nchar(13)+nchar(10)+@tmp
-- <SQL_7>
set @tmp = @tmp+nchar(13)+nchar(10)+N'select identity(int,1,1) As ResultRow,* into '+@tmpTable2+N' from '+@tmpTable
set @tmp = @tmp+nchar(13)+nchar(10)+N'drop table '+@tmpTable
set @tmp = @tmp+nchar(13)+nchar(10)+N'set rowcount '+cast(@pageSize As varchar(10))
set @tmp = @tmp+nchar(13)+nchar(10)+N'select * from '+@tmpTable2+N' where ResultRow > '+convert(nvarchar(10),@offset)
-- </SQL_7>
-- <SQL_2000>
-- set @tmp = @tmp+char(13)+char(10)+'select * from '+@tmpTable+' where ResultRow > '+convert(varchar(10),@offset)
-- </SQL_2000>
set @tmp = @tmp+nchar(13)+nchar(10)+N'set rowcount 0'
-- <SQL_7>
set @tmp = @tmp+nchar(13)+nchar(10)+N'drop table '+@tmpTable2
-- </SQL_7>
-- <SQL_2000>
-- set @tmp = @tmp+nchar(13)+nchar(10)+N'drop table '+@tmpTable
-- <SQL_2000>
if  @returnRowCount <> 0
begin
    set @tmp = @tmp+nchar(13)+nchar(10)+@countSelect
end
set @tmp = @tmp+nchar(13)+nchar(10)+N'set nocount off'
end --@offset <> 0 and @pageSize <> 0
-- ***************************************************************************************
-- Execute
-- ***************************************************************************************
exec(@tmp)
end

GO